﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;

namespace tools.office.npoi
{
    /// <summary>
    /// NPOI Excel 导入工具类
    /// </summary>
    public static class NpoiExcelImportTool
    {
        /// <summary>
        /// 读取 Excel 文件转换为 DataTable
        /// </summary>
        /// <param name="filePath">Excel 文件完整路径</param>
        /// <returns>DataTable 实例</returns>
        public static DataTable GetDataTable(string? filePath)
        {
            if (!File.Exists(filePath))
            {
                throw new InvalidOperationException("Excel 文件不存在");
            }

            using IWorkbook wb = Path.GetExtension(filePath) switch
            { 
                ".xlsx" => new XSSFWorkbook(filePath, true),
                ".xls" => new HSSFWorkbook(new FileStream(filePath, FileMode.Open, FileAccess.Read)),
                _ => throw new InvalidOperationException("无效的 Excel 文件后缀")
            };

            var dt = new DataTable();
            var sheet = wb.GetSheetAt(0);

            dt.BeginLoadData(); // 关闭通知、索引维护和约束

            try
            {
                #region 处理标题行

                var titleRow = sheet.GetRow(0);

                // 多次使用的属性最好用变量存起来调用，一个是改动方便，另一个属性是计算出的话能省去计算消耗
                int columnCount = titleRow.LastCellNum;

                for (int i = 0; i < columnCount; i++)
                {
                    string title = titleRow.GetCell(i)?.StringCellValue ?? "空列" + i;
                    dt.Columns.Add(title);
                }

                #endregion

                #region 处理数据行

                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    var row = sheet.GetRow(i);
                    if (row == null) continue;

                    var dtRow = dt.NewRow();
                    bool isActiveRow = false;

                    for (int j = 0; j < columnCount; j++) // 以标题列的列数为基准，其他列不考虑
                    {
                        var cell = row.GetCell(j);
                        if (cell == null || cell.CellType == CellType.Blank)
                        {
                            dtRow[j] = DBNull.Value;
                        }
                        else
                        {
                            // 数据格式转换
                            dtRow[j] = cell.CellType switch
                            {
                                CellType.String => cell.StringCellValue,
                                CellType.Numeric when DateUtil.IsCellDateFormatted(cell) =>
                                    cell.DateCellValue?.ToString("yyyy-MM-dd HH:mm:ss"),
                                CellType.Numeric => cell.NumericCellValue,
                                _ => "意外的数据格式"
                            };

                            isActiveRow = true;
                        }
                    }

                    if (isActiveRow) // 当前行存在有效值才导出
                    {
                        dt.Rows.Add(dtRow);
                    }
                }

                #endregion

                return dt;
            }
            finally
            {
                dt.EndLoadData();
            }
        }
    }
}
